package com.gtfrank.web.servlet.util;

import org.apache.commons.beanutils.BeanUtils;

import javax.swing.plaf.basic.BasicInternalFrameTitlePane;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ProjectName: jdbcourse
 * @ClassName: BasicDao
 * @PackageName:com.jdbc.util
 * @Description: JDBC通用方法
 * @Author: frank
 * @Date: 2021/3/25 16:06
 * @Version v1.0
 */
public class BasicDao {
    //支持  insert  update  delete 这三种sql语句 及其不同sql参数个数不一样

    /**
     * 1:因为我不知道你执行什么sql语句，所有你传入sql
     * 2:你的参数个数不确定   什么容器可以 存放 >=1 集合 数组  可变长参数【String... args】
     * 此代码还不是最终版本，等讲到事务的时候 会把代码更新
     */
    public static void updateData(String sql,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection  = JdbcUtil.getConnection();
            //我设置了手动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            //迭代可变长参数
            for(int i =0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            int i = preparedStatement.executeUpdate();
            System.out.println(i+"条记录被影响");
            //事务提交
            connection.commit();
        }catch (Exception e){
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtil.relase(null,preparedStatement,connection);
        }

    }
    public static void updateDataWithTran(Connection connection,String sql,Object... args){

        PreparedStatement preparedStatement = null;
        try{
            //我设置了手动提交
            //connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            //迭代可变长参数
            for(int i =0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            int i = preparedStatement.executeUpdate();
            System.out.println(i+"条记录被影响");
            //事务提交
            //connection.commit();
        }catch (Exception e){
            /*try {
                //connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }*/
            e.printStackTrace();
        }finally {
            JdbcUtil.relase(null,preparedStatement,null);
        }

    }
    //1: 查询返回单列
    public  static <E> E  getColumnValue(String sql ,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for(int  i = 0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return (E)resultSet.getObject(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.relase(resultSet,preparedStatement,connection);
        }
        return null;
    }

    //2:返回单个对象 考虑参数是什么

    /**
     * @param <T>
     * @return T
     *  String sql      Object...
     *  返回 肯定是一个对象  User Product    --用反射-----Class
     */
    public  static <T> T getObject(Class<T> clazz,String sql,Object... args){
        T object = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ResultSetMetaData resultSetMetaData = null;
        try{
            connection = JdbcUtil.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            //给sql赋值
            for(int i = 0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            //你要查的数据都在resultSet
            resultSet = preparedStatement.executeQuery();
            /**
             * 1:我不知道数据库有几列
             * 2：我想办法得到列的值
             * 3：还要得到列名
             */
            resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            //需要map集合 key 存放列名  value 值
            Map<String,Object> map = new HashMap<String,Object>();
            if(resultSet.next()){
                for(int i = 0;i<columnCount;i++){
                    // 这个方法前提是JavaBean的属性名要和列名相同，或者是你sql别名要和属性相同
                    String labName = resultSetMetaData.getColumnLabel(i+1);
                    Object value = resultSet.getObject(labName);
                    map.put(labName,value);
                }
            }
            if(map.size()>0){
                //实例化
                    object = clazz.newInstance();
                    //给属性赋值
                    for(Map.Entry<String,Object> maps:map.entrySet()){
                        //取得属性名  NAME --name  setName()
                        String name = maps.getKey().toLowerCase();
                        Object value = maps.getValue();
                        BeanUtils.setProperty(object,name,value);
                    }
            }
        }catch (Exception e){
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtil.relase(resultSet,preparedStatement,connection);
        }

        return object;
    }
    //3: 返回对象的集合考虑参数是什么
    public  static <T> List<T> getObjectToList(Class<T> clazz,String sql,Object... args){
        List<T> list  = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ResultSetMetaData resultSetMetaData = null;
        try{
            connection = JdbcUtil.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            for (int i =0 ;i< args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            resultSetMetaData = resultSet.getMetaData();
            Map<String,Object> map = null;
            List<Map<String,Object>> values = new ArrayList<Map<String,Object>>();
            while (resultSet.next()){
                map = new HashMap<String,Object>();
                for (int i =0;i<resultSetMetaData.getColumnCount();i++){
                    String name = resultSetMetaData.getColumnLabel(i+1);
                    Object value = resultSet.getObject(name);
                    map.put(name,value);
                }
                values.add(map);
            }
            T object = null;
            if(values.size()>0){
                for (Map<String,Object> map2:values){
                    object = clazz.newInstance();
                    for (Map.Entry<String,Object> ma:map2.entrySet()){
                        String name = ma.getKey().toLowerCase();
                        Object value = ma.getValue();
                        BeanUtils.setProperty(object,name,value);
                    }
                    list.add(object);
                }
            }
            connection.commit();
        }catch(Exception e){

            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            JdbcUtil.relase(resultSet,preparedStatement,connection);
        }
        return list;
    }
    /**
     * 列名=值
     * @param resultSet
     */
    public static void  printResult(ResultSet resultSet){
        StringBuffer jpsb = new StringBuffer();
        try{
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int count = resultSetMetaData.getColumnCount();
            while(resultSet.next()){
                jpsb.append("[");
                for (int i =1;i<=count;i++){
                    jpsb.append(resultSetMetaData.getColumnName(i)+" =" +
                            ""+resultSet.getObject(resultSetMetaData.getColumnName(i))+"  ");
                }
                jpsb.append("]").append("\n");
            }
            System.out.println(jpsb.toString());
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}
